How to persist checkbox selections across recordset pages

Thanks Jim,
Actually myself and dave having been discussing about the option you
suggested.
The only question in my mind is :- On clicking of the next and prev the form
is submitted. How much impact this has on performance instead of just calling
the same page url (in href) with different PAGE QUERYSTRING value.

Looking forward to your response.
thanks and regards,
paarg


"Jim Rodgers" wrote:

> "Parag Gaikwad" wrote:
>
> > Hi All,
> > I have a large recordset to be displayed on a ASP 3.0 page. I am using
> > recordset paging for this. For the next and previous link i am passing href
> > as
> > ?page=<%= iPageCurrent - 1 %> for Previous.
> > Now when i display the contents i also add a checkbox (for deletion) to each
> > of the records.
> > Now user should be able to select one or more checkboxes across pages and
> > then should be allow to delete all selections together.
> > Can anyone please point me right directions or give some hints on how to
> > acheive this.
> > Thanks and Regards,
> > Parag
>
> Parag,
>
> It seems to me you would like to use HTML forms. If you
> can put a checkbox on every record, then you are already
> building a form. I assume you have some way of assigning
> names to each checkbox so it exactly identifies which record
> needs to be deleted. I would use the primary key of the
> table; that would simplify the SQL for deleting the record.
>
> The nice thing about forms and inputs is you don't have to
> display the primary key. Rather, just use it as the name of
> the checkbox.
>
> Next, add HTML at the top of the page before the

"Parag Gaikwad" wrote:
> The only question in my mind is :- On clicking of the next and prev the form
> is submitted. How much impact this has on performance instead of just calling
> the same page url (in href) with different PAGE QUERYSTRING value.

Parag,

I prepared a tutorial for you. This took a long time. I didn't
have the time also to go back and check everything Dave told
you, so I don't if is redundant, or in conflict or what. Please
read this carefully, and I apologise if I'm telling stuff you
already know. I want to be helpful. It is my way of paying
back this newsgroup for helping me with hard problems (and
with easy ones, too.)

Before I start let me go ahead and say this: there are no
performance differences to worry about. Actually, you may
have a lot of work to do. Performance is the least of your
problems.

======================================
Links and the Two Types of Forms
======================================

Clicking on a Submit button when the form is like this...






-IS- the -EXACT- same thing as clicking on a link where...


Next Page


....assuming that only checkbox #2 is checked.

So, these two are the same. But, this is also the same as
when method="post" except for two things:

(1) The "post" method hides the data so the user will not
see it in the URL. With the href or with method="get",
the data appears on the URL when the user gets to the
next page.

(2) Usually putting the data into the URL is not important,
but it can be if the length limit of URL (the QUERYSTRING
limit) also limits how many rows you can delete. Which
it does. NOTE: method="post" does not put the data
into the URL QUERYSTRING. It puts it into the IP packets
somewhere that more data is allowed than on the URL.

Another difference is you use different ASP functions when
you read the data on the "next page." Here are the functions:

— for forms with method="get" and for links (href="...")

strControlValue = Request.QueryString("strControlName")

— and for forms with method="post"

strControlValue = Request.Form("strControlName")

....where "strControlName" is the value you had in name=""
on the checkbox (or any other input of the form). And the
value="" parameter will go into strControlValue on the "next
page."

======================================
How to Structure an ASP file
======================================

I say "next page" because it is the same page, of course. Go
back and read my examples again. Try to understand every
line. The first thing you do on this page is to determine what
happenned that brought you to this page. Did someone enter
the page name in the address bar of their browser? Did the
user click on "Previous"? On "Next"? You need to figure this
out first thing at the beginning of your asp page.

Now, from a programming point of view, there are many ways
to go through the data once you are back again after clicking
on the "Next" or "Previous" button. Let's talk about that for a
minute.

This is HTML: When you have many checkboxes on a page,
and only some of them are checked, it is these checked ones
that appear in the data. The unchecked boxes are ignored.
The checked boxes appear on the next page as either values
in the Query string or as values in the Form collection.

In the Query string, of course, they look like this:
myPage.asp?name1=value1&name2=value2&name2=value2

In the Form(i) collection, you have to "look" at each Form(i).

Because you don't know the names of the checkboxes that
were checked beforehand, part of what you have to do is to
determine which ones if any were checked. So, you go
through the Query string or through the Form(i)'s until you
get to the end.


======================================
How to Delete a Bunch of Records
======================================

In my previous example, I described how you can
read the Form data from Request.Form(i) and delete the
records. That technique was BACKWARDS from what a lot
of people know. Here, I will show you the "forwards"
way to do this. Both work well. It's personal.
Most programmers would probably prefer the following
technique for your particular problem.

Suppose you named every checkbox with the same name,
and that is name="delete". And each value was something
like value="record1" and value="record2" and like that.
Then what you find when you get to the next page is this...

delete=record1,record2,record3 ...and so forth

(assuming these are the checkboxes you checked)

You can use the VBScript function Split() to make an array
of records_to_delete like this:

Dim strTemp, strRecords
strTemp = Request.Form("delete")
strRecords = Split(strTemp,",")

or if you use href="myPage.asp" (or method="get") then

Dim strTemp, strRecords
strTemp = Request.QueryString("delete")
strRecords = Split(strTemp,",")

....no real differences between them except the limitations in
length you know about already. Split() makes strRecords into
an array containing all the things in strTemp that were
separated by commas.

When you want to delete a file you can do this...

Dim myConnection, iRec, nDeleted
myConnection = Server.CreateObject("ADODB.Connection")
For iRec = 1 to UBound(records)
myConnection.Execute "DELETE * FROM myTable " & _
"WHERE priKey='" & records(iRec) & "';" _
, nDeleted
Next ' delete each record one at a a time

It depends on the database you use, but I think you might be
able to do the following if you want to delete them all at once:

Dim strSQL, iRec, nDeleted
If UBound(strRecords)>0 Then
strSQL = "DELETE * FROM myTable WHERE priKey IN ("
strSQL = strSQL & " '" & strRecords(iRec) & "'"
For iRec = 2 to UBound(strRecords)
strSQL = ", '" & strRecords(iRec) & "'"
Next
strSQL = strSQL & " );"
myConnection.Execute strSQL, nDeleted
Response.Write "
" & CStr(nRecords) & _
" were deleted.
" & vbCrLf
End If

I tested this for you and it WORKS with an MSAccess database
file (.mdb) assuming that "priKey" is the name of your primary
key and it is a string. Leave out the single quotes if it
is not a string.

If the priKey value were not strings, you could even
do this:

Dim strTemp, strRecords, strSQL, iRec, nDeleted
strTemp = Request.Form("delete")
strRecords = Split(strTemp,",")
If UBound(strRecords)>0 Then
strSQL = "DELETE * FROM myTable " & _
"WHERE priKey IN (" & strTemp & " );"
myConnection.Execute strSQL, nDeleted
Response.Write "
" & CStr(nRecords) & _
" were deleted.
" & vbCrLf
End If

In fact, if strKey values WERE strings, this should work:

Dim strTemp, strRecords, strSQL, iRec, nDeleted
strTemp = Request.Form("delete")
strRecords = Split(strTemp,",")
strTemp = "'" & Replace(strTemp, ",", "','") & "'"
If UBound(strRecords)>0 Then
strSQL = "DELETE * FROM myTable " & _
"WHERE priKey IN (" & strTemp & " );"
myConnection.Execute strSQL, nDeleted
Response.Write "
" & CStr(nRecords) & _
" were deleted.
" & vbCrLf
End If


======================================
A Few More Ideas For the Webpage
======================================

You could have on each page THREE buttons. All of
them have name="Choice" loike I mention in my previous
message. One button is to DELETE the records which
are checked, and the other two for the Next and Previous
functions.

Putting it all together, this the new part of your
new asp file:


'
Dim strTemp, strRecords, strSQL, iRec, nDeleted
Dim myConnection
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open "...Connection String goes here..."
'
'
' First, take care of the records.
'
'
Select Case UCase(Request.Form("Choice"))
Case "DELETE"
'
strTemp = Request.Form("delete")
strRecords = Split(strTemp,",")
strTemp = "'" & Replace(strTemp, ",", "','") & "'"
If UBound(strRecords)>0 Then
strSQL = "DELETE * FROM myTable " & _
"WHERE priKey IN (" & strTemp & " );"
myConnection.Execute strSQL, nDeleted
Response.Write "
" & CStr(nRecords) & _
" were deleted.
" & vbCrLf
Else
Response.Write "
NO records were deleted.
" & vbCrLf
End If
End Select
'
'
'
' Next, render the correct page of records.
'
'
Select Case UCase(Request.Form("Choice"))
Case "PREVIOUS"
'
' render the "PREVIOUS" page of records
'
Case "NEXT"
'
' render the "NEXT" page of records
'
Case "DELETE"
'
' render the "SAME" page of records again
'
Case Else ' you got here from ANOTHER page!
'
' render the "FIRST" page of records
'
End Select
'
'
myConnection.Close
Set myConnection = Nothing


Good Luck Parag,

Jim Rodgers




======= An Aside on References =======

By the way, good VBScript and ASP Classic references are
the following:

http://msdn2.microsoft.com/en-us/library/d1wf56tt.aspx
http://www.w3schools.com/vbscript/default.asp
http://msdn2.microsoft.com/en-us/library/ms524664.aspx
http://www.w3schools.com/asp/default.asp

The MSDN references are deteriorating rapidly. MS seems to
be circling the drain under new management. It is fantastically
ironic that the w3schools sites are even on this short list!
Others in this newsgroup will give you more great references.

======================================

Re: How to persist checkbox selections across recordset pages

Thanks a lot Dave.
I have not got it working both ways the Submit and cookie way.
Once again many thanks for helping me out.
Best Regards,
Parag

RE: How to persist checkbox selections across recordset pages

Thanks a lot Jim.
Your inputs were very useful.
I have got it working both ways i.e. submit and cookie.
Once again thanks for the help.
Best Regards,
Parag